﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Data.SqlClient;

namespace MeterControl.Services
{
    public class DBClass
    {
        #region 数据库连接及连接字符串
        private static string ConnStr = System.Configuration.ConfigurationManager.AppSettings["dbstring"];
        private static string ConnStr1 = System.Configuration.ConfigurationManager.AppSettings["dbstring1"];
        public static SqlConnection Conn
        {
            get
            {
                return new SqlConnection(ConnStr);
            }
        }


        public static SqlConnection Conn1
        {
            get
            {
                return new SqlConnection(ConnStr1);
            }
        }

        #endregion	

        #region Insert
        /// <summary>
        ///  插入
        /// </summary>
        /// <param name="oHashtable">Hashtable</param>
        /// <param name="strTable">表名</param>
        public static int Insert(Hashtable oHashtable, string strTable)
        {
            int flag = 1;

            string sql = "", sqla = "", sqlb = "@";
            string[] arrFields = new string[oHashtable.Count];
            SqlConnection conn = Conn;
            try
            {
                oHashtable.Keys.CopyTo(arrFields, 0);
                sqla = String.Join(",", arrFields);
                sqlb += String.Join(",@", arrFields);
                sql = "insert into[" + strTable + "](" + sqla + ") values(" + sqlb + ")";

                SqlCommand oComm = new SqlCommand(sql, conn);

                foreach (string field in oHashtable.Keys)
                {
                    oComm.Parameters.Add(new SqlParameter("@" + field, oHashtable[field]));
                }

                conn.Open();
                oComm.ExecuteNonQuery();
                flag = 1;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return flag;
        }

        #endregion

        #region Update
        /// <summary>
        ///  更新
        /// </summary>
        /// <param name="oHashtable">Hashtable</param>
        /// <param name="strTable">表名</param>
        public static int Update(Hashtable oHashtable, string strTable, string Key, int Id)
        {
            int flag = 0;

            string sql = "", sqla = "";
            string[] arrFields = new string[oHashtable.Count];
            SqlConnection conn = Conn;
            try
            {
                oHashtable.Keys.CopyTo(arrFields, 0);
                for (int i = 0; i < arrFields.Length; i++)
                {
                    sqla += "[" + arrFields[i] + "]=@" + arrFields[i];

                    if (i != arrFields.Length - 1) sqla += ",";
                }

                sql = "Update [" + strTable + "] Set " + sqla + " where " + Key + "=" + Id;


                SqlCommand oComm = new SqlCommand(sql, conn);

                foreach (string field in oHashtable.Keys)
                {
                    oComm.Parameters.Add(new SqlParameter("@" + field, oHashtable[field]));
                }

                conn.Open();
                oComm.ExecuteNonQuery();
                flag = 1;
            }

            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return flag;
        }

        #endregion

        /// <summary>
        /// 批量修改
        /// </summary>
        /// <param name="ds">DataSet</param>
        /// <param name="sql">对比的语句</param>
        /// <param name="tbName">表名</param>
        /// <returns>DataSet</returns>
        public static DataSet UpdateAll(DataSet ds, string sql, string tbName)
        {

            SqlConnection conn = null;
            try
            {
                conn = Conn;
                SqlDataAdapter adp = new SqlDataAdapter();
                SqlCommand myCommand = new SqlCommand(sql, conn);
                adp.SelectCommand = myCommand;
                SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(adp);
                adp.UpdateCommand = myCommandBuilder.GetUpdateCommand();
                adp.Update(ds);
                ds.AcceptChanges();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            return ds;
        }


        #region 取首行首列数据
        /// <summary>
        /// 取首行首列数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string ExecuteScalar(string sql)
        {
            SqlConnection conn = Conn;
            try
            {
                SqlCommand oComm = new SqlCommand(sql, conn);
                conn.Open();
                return Convert.ToString(oComm.ExecuteScalar());

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion


        /// <summary>
        /// 查询数量，返回一个int型
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static int ExcuteIntScalar(string strSql)
        {
            SqlConnection conn = Conn;
            SqlCommand cmd = new SqlCommand(strSql, conn);
            if (conn.State.ToString() == "Closed")
                conn.Open();

            int count = 0;
            try
            {
                count = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());

            }
            return count;
        }

        #region Get DataTable
        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="AccSql"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string AccSql)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = Conn;
            SqlDataAdapter dr = null;
            try
            {
                dr = new SqlDataAdapter(AccSql, conn);
                dr.Fill(dt);
            }
            catch { }
            finally
            {
                if (conn != null)
                {
                    conn.Dispose();
                }
                if (dr != null)
                {
                    dr.Dispose();
                }
            }
            return dt;
        }




        public static DataTable GetDataTable1(string AccSql)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = Conn1;
            SqlDataAdapter dr = null;
            try
            {
                dr = new SqlDataAdapter(AccSql, conn);
                dr.Fill(dt);
            }
            catch { }
            finally
            {
                if (conn != null)
                {
                    conn.Dispose();
                }
                if (dr != null)
                {
                    dr.Dispose();
                }
            }
            return dt;
        }

        #endregion

        #region Get DataSet
        /// <summary>
        /// Get DataSet
        /// </summary>
        /// <param name="AccSql"></param>
        /// <returns></returns>
        public static DataSet GetDataset(string AccSql)
        {
            DataSet ds = new DataSet();
            SqlConnection conn = Conn;
            SqlDataAdapter dr = null;

            try
            {
                dr = new SqlDataAdapter(AccSql, conn);
                dr.Fill(ds);
            }
            catch { }
            finally
            {
                if (conn != null)
                {
                    conn.Dispose();
                }
                if (dr != null)
                {
                    dr.Dispose();
                }
            }
            return ds;
        }
        #endregion

        #region SQL 常规操作
        /// <summary>
        /// insert ,update ,delete ;No return value 
        /// </summary>
        /// <param name="sql"></param> 
        public static void ExecuteNonQuery(string sql)
        {
            SqlConnection conn = Conn;
            try
            {
                SqlCommand oComm = new SqlCommand(sql, conn);
                conn.Open();
                oComm.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                //throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }


        /// <summary>
        /// insert ,update ,delete ;No return value 
        /// </summary>
        /// <param name="sql"></param> 
        public static void ExecuteNonQuery1(string sql)
        {
            SqlConnection conn = Conn1;
            try
            {
                SqlCommand oComm = new SqlCommand(sql, conn);
                conn.Open();
                oComm.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                //throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }



        public static int ExeNonQuery1(string sql)
        {
            int result = 0;
            SqlConnection conn1 = Conn1;
            try
            {
                SqlCommand oComm = new SqlCommand(sql, conn1);
                conn1.Open();
                result = oComm.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn1.Close();
            }
            return result;
        }


        /// <summary>
        /// insert ,update ,delete ; return value 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExeNonQuery(string sql)
        {
            int result = 0;
            SqlConnection conn = Conn;
            try
            {
                SqlCommand oComm = new SqlCommand(sql, conn);
                conn.Open();
                result = oComm.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        #endregion

        #region 将数据从Excel中批量导入到Access中
        public static void ExcelToAccess(string TableName, string DataFields, string ExcelFilePath)
        {
            try
            {
                SqlConnection conn = Conn;
                string sql = "insert into [" + TableName + "] (" + DataFields + ") select " + DataFields + " from [Sheet1$] in '" + ExcelFilePath + "' 'Excel 8.0;' ";
                SqlCommand oComm = new SqlCommand(sql, conn);
                conn.Open();
                oComm.ExecuteNonQuery();
            }
            catch
            { }
        }
        #endregion
    }
}
